package com.example.spring.mapper;

import com.example.spring.entity.Record;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface RecordMapper {
    @Select("SELECT * FROM record")
    List<Record> findAll();

    @Insert("INSERT INTO record(sid, whid, gid, event, create_time) VALUES(#{sid}," +
            "#{whid}, #{gid}, #{event}, #{createTime})")
    int insert_data(Record record);

    int update(Record record);

    @Delete("DELETE FROM record WHERE rid = #{rid}")
    Integer deleteById(@Param("rid") Integer rid);

    @Select("SELECT * FROM record WHERE create_time <= #{maxTime} AND create_time >= #{minTime} AND sid != 0")
    List<Record> findByTime(@Param("maxTime")Timestamp maxTime, @Param("minTime")Timestamp minTime);

    //查询某仓库某时间以后的记录
    @Select("SELECT * FROM record WHERE create_time >= #{minTime} AND whid = #{whid} AND sid != 0 ORDER BY create_time DESC")
    List<Record> findByMinTimeWhid(@Param("minTime")Timestamp minTime,@Param ("whid") Integer whid);

    //查询某仓库某时间以后的记录
    @Select("SELECT * FROM record WHERE create_time >= #{minTime} AND create_time <= #{maxTime} " +
            "AND whid = #{whid} AND sid != 0 ORDER BY create_time DESC")
    List<Record> findByTimePeriodWhid(@Param("minTime")Timestamp minTime,
                                      @Param("maxTime")Timestamp maxTime,
                                      @Param ("whid") Integer whid);

    @Select("SELECT * FROM record AS r, good AS g WHERE r.gid = g.gid AND " +
            "r.create_time >= #{minTime} AND r.whid = #{whid} AND g.type = #{type} AND r.sid != 0 " +
            "ORDER BY r.create_time DESC")
    List<Record> findByMinTimeTypeWhid(@Param("minTime") Timestamp minTime,
                                       @Param("type") String type,
                                       @Param("whid") Integer whid);

    @Select("SELECT * FROM record WHERE create_time >= #{minTime} AND sid = #{sid} " +
            "ORDER BY create_time DESC")
    List<Record> findByMinTimeSlot(@Param("minTime") Timestamp minTime,
                                   @Param("sid") Integer sid);

    @Select("SELECT * FROM record WHERE sid = #{sid} AND sid != 0")
    List<Record> findBySlot(@Param("sid") Integer sid);

    @Select("SELECT * FROM record NATURAL JOIN good WHERE type = #{type} AND sid != 0")
    List<Record> findByType(@Param("type") String type);

    @Select("SELECT * FROM record WHERE sid != 0 LIMIT #{pageNum}, #{pageSize}")
    List<Record> find_all_Page(@Param("pageNum") Integer pageNum,@Param("pageSize") Integer pageSize);

    @Select("SELECT count(*) FROM record WHERE sid != 0")
    Integer find_all_Total();

    @Select("SELECT * FROM record WHERE create_time <= #{maxTime} AND create_time >= #{minTime} AND sid != 0" +
            "LIMIT #{pageNum}, #{pageSize}")
    List<Record> findByTime_page(@Param("maxTime")Timestamp maxTime, @Param("minTime")Timestamp minTime,
                                    @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);
    @Select("SELECT * FROM record WHERE sid = #{sid} LIMIT #{pageNum}, #{pageSize} ")
    List<Record> findBySlot_page(@Param("sid")Integer sid, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT * FROM record, good WHERE record.gid = good.gid AND good.type = #{type} AND record.sid != 0 " +
            "LIMIT #{pageNum}, #{pageSize}")
    List<Record> findByType_page(@Param("type")String type, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT COUNT(*) FROM record WHERE create_time <= #{maxTime} AND create_time >= #{minTime} AND sid != 0")
    Integer findByTime_Total(@Param("maxTime")Timestamp maxTime, @Param("minTime")Timestamp minTime);

    @Select("SELECT COUNT(*) FROM record WHERE sid = #{sid}")
    Integer findBySlot_Total(@Param("sid")Integer sid);

    @Select("SELECT COUNT(*) FROM record, good WHERE record.gid = good.gid AND good.type = #{type} AND record.sid != 0")
    Integer findByType_Total(@Param("type")String type);

    @Select("SELECT * FROM record AS r, good AS g " +
            "WHERE r.gid = g.gid AND r.sid != 0 "+ "${str} "+
            "LIMIT #{pageNum}, #{pageSize}")
    List<Record> select_page(@Param("str") String str, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT COUNT(*) FROM record AS r, good AS g  " +
            "WHERE r.gid = g.gid  AND r.sid != 0 " + "${str} ")
    Integer select_Total(@Param("str") String str);

    //找到某仓库中的货物种类
    @Select("SELECT DISTINCT type FROM good AS g, record AS r WHERE g.gid = r.gid AND r.whid = #{whid} AND r.sid != 0")
    List<String> find_history_types_wh(@Param("whid") Integer whid);

    //找到某slot所有记录中出现过的type
    @Select("SELECT DISTINCT type FROM good WHERE sid = #{sid} AND sid != 0")
    List<String> find_history_types_slot(@Param("sid") Integer sid);

}
